﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Data.Entity;
using Aspose.GmailSync.Data;

namespace Aspose.GmailSync.Components
{
    public class DatabaseHelper
    {
        static GmailSyncEntities gmailSyncEntities;

        private static GmailSyncEntities CurrentDBEntities
        {
            get
            {
                if (gmailSyncEntities == null)
                {
                    string providerName = ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ProviderName;
                    SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
                    sqlBuilder.ConnectionString = ConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;
                    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
                    entityBuilder.Provider = providerName;
                    entityBuilder.ProviderConnectionString = sqlBuilder.ToString();

                    entityBuilder.Metadata = "res://*/Data.GmailSyncModel.csdl|res://*/Data.GmailSyncModel.ssdl|res://*/Data.GmailSyncModel.msl";
                    EntityConnection entityConnection = new EntityConnection(entityBuilder.ToString());

                    gmailSyncEntities = new GmailSyncEntities(entityBuilder.ToString());

                    // Check if table exists and create it if it does not
                    try
                    {
                        string sql = @"IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Aspose_GmailSync_ServerDetails' AND TABLE_TYPE = 'BASE TABLE' )
	                                    BEGIN
		                                    CREATE TABLE Aspose_GmailSync_ServerDetails(
			                                    ID int IDENTITY(1,1) NOT NULL,
			                                    UserID int NOT NULL,
			                                    Username nvarchar(250) NULL,
			                                    Email nvarchar(250) NULL,
			                                    Password nvarchar(250) NULL,
			                                    ClientID nvarchar(250) NULL,
			                                    ClientSecret nvarchar(250) NULL,
			                                    RefreshToken nvarchar(500) NULL
		                                    )
	                                    END";

                        using (SqlConnection objConnection = new SqlConnection(sqlBuilder.ConnectionString))
                        {
                            objConnection.Open();
                            var command = new SqlCommand(sql, objConnection);
                            command.ExecuteNonQuery();
                            objConnection.Close();
                        }
                    }
                    catch (Exception)
                    {}
                }
                return gmailSyncEntities;
            }
        }

        private static string GmailDetailsSessionName(int userId)
        {
            return "GmailDetailsSession-" + userId.ToString();
        }

        public static Aspose_GmailSync_ServerDetails CheckGmailDetails(int userID)
        {
            Aspose_GmailSync_ServerDetails detailsToReturn = null;
            try
            {
                Aspose_GmailSync_ServerDetails serverDetails = CurrentDBEntities.Aspose_GmailSync_ServerDetails.FirstOrDefault(x => x.UserID == userID);
                if (serverDetails != null)
                {
                    detailsToReturn = new Aspose_GmailSync_ServerDetails();
                    detailsToReturn.UserID = serverDetails.UserID;
                    detailsToReturn.Username = serverDetails.Username;
                    detailsToReturn.Email = serverDetails.Email;
                    detailsToReturn.Password = Crypto.Decrypt(serverDetails.Password);
                    detailsToReturn.ClientID = Crypto.Decrypt(serverDetails.ClientID);
                    detailsToReturn.ClientSecret = Crypto.Decrypt(serverDetails.ClientSecret);
                    detailsToReturn.RefreshToken = Crypto.Decrypt(serverDetails.RefreshToken);
                }
            }
            catch (Exception)
            {
                // Fall back to session approach if database fails
                if (HttpContext.Current.Session[GmailDetailsSessionName(userID)] != null)
                {
                    detailsToReturn = (Aspose_GmailSync_ServerDetails)HttpContext.Current.Session[GmailDetailsSessionName(userID)];
                }
            }
            return detailsToReturn;
        }

        public static void AddUpdateServerDetails(Aspose_GmailSync_ServerDetails details)
        {
            try
            {
                Aspose_GmailSync_ServerDetails serverDetails = CurrentDBEntities.Aspose_GmailSync_ServerDetails.FirstOrDefault(x => x.UserID == details.UserID);

                if (serverDetails != null)
                {
                    serverDetails.UserID = details.UserID;
                    serverDetails.Username = details.Username;
                    serverDetails.Email = details.Email;
                    serverDetails.Password = details.Password;
                    serverDetails.ClientID = details.ClientID;
                    serverDetails.ClientSecret = details.ClientSecret;
                    serverDetails.RefreshToken = details.RefreshToken;
                    CurrentDBEntities.SaveChanges();
                }
                else
                {
                    CurrentDBEntities.Aspose_GmailSync_ServerDetails.Add(details);
                    CurrentDBEntities.SaveChanges();
                }
            }
            catch (Exception)
            {
                // Fall back to session approach if database fails
                details.Password = Crypto.Decrypt(details.Password);
                HttpContext.Current.Session[GmailDetailsSessionName((int)details.UserID)] = details;
            }
        }
    }
}